package com.ibm.risk.irmp.data.table.service.impl;
/**
 * wjx for table table DDL
 */

import com.ibm.risk.irmp.common.SystemConstants;
import com.ibm.risk.irmp.common.auth.AuthUtils;
import com.ibm.risk.irmp.common.auth.vo.LoginUser;
import com.ibm.risk.irmp.common.vo.CodeLabelVo;
import com.ibm.risk.irmp.common.vo.PagedParam;
import com.ibm.risk.irmp.data.table.dao.TableColumnDefRepository;
import com.ibm.risk.irmp.data.table.dao.TableDefRepository;
import com.ibm.risk.irmp.data.table.entity.DataTable;
import com.ibm.risk.irmp.data.table.entity.DataTableColumn;
import com.ibm.risk.irmp.data.table.service.IDataTableQueryService;
import com.ibm.risk.irmp.data.table.vo.ColumnFilterVo;
import com.ibm.risk.irmp.data.table.vo.TableDataQueryParam;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.ResultSetMetaData;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
@Service
@Transactional(readOnly = true)
//@CacheConfig(cacheNames = "IrmpParamDataCache")
public class DataTableServiceImpl implements IDataTableQueryService {

    @Value("${app.irmp.base.orgs.HQ_BIZ_BRANCH_CODE}")
    String hqFinBranch;

    @Value("${app.irmp.base.orgs.HQ_MNG_BRANCH_CODE}")
    String hqMngBranch;

    @Autowired
    TableDefRepository tableDefRepository;

    @Autowired
    TableColumnDefRepository tableColumnDefRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;

//    private List<DataTable> tableDefList;
//	private List<DataTableColumn> tableColumnDefList = new ArrayList<>();
//    private Map<String, List<DataTableColumn>> tableColumnDefMap = new HashMap<>();


//    public DataTableServiceImpl() {
//
//    }


    @Override
//    //@Cacheable(key = "'DataTable:' + methodName")
    public List<DataTable> getTableDefList() {
        List<DataTable> dataTableList = tableDefRepository.findAll();
        return dataTableList;
    }

//    @Override
//    public List<DataTableColumn> getTableColumnDefList() {
//        return tableColumnDefList;
//    }

    @Override
//    //@Cacheable(key = "'DataTable:' + methodName + args")
    public List<DataTable> getTableListByGroup(String groupCode) {

        List<DataTable> dataTableList = tableDefRepository.findByGroup(groupCode);
        return dataTableList;
    }

    @Override
    //@Cacheable(key = "'DataTableColumn:' + methodName + args")
    public List<DataTableColumn> getTableColumnDefListByTableCode(String tableCode) {
        List<DataTableColumn> dataTableColumns = tableColumnDefRepository.findByTableCode(tableCode);
        return dataTableColumns;
    }


    @Override
    public Map<String, Object> getTableData(String tableCode, TableDataQueryParam qparam) {
        PagedParam page = qparam.getPagedParam();
        List<ColumnFilterVo> queryParams = qparam.getColumnFilters();
        String dataDate = qparam.getDataDate();
//        String dataDate = (String) queryParams.get("dataDate");
        //1.获得用户userId, 获得账户机构
        //2.拼装sql
        //3.返回值.
        if (page.getPageSize() == null || page.getPageSize() <= 0) {
            page.setPageSize(500);
        }
        tableCode = tableCode.toUpperCase(Locale.ROOT);
        DataTable dataTable = tableDefRepository.findById(tableCode).get();
        //获得data字段，财务机构的字段
        List<DataTableColumn> columnDefList = tableColumnDefRepository.findByTableCode(tableCode);
        Optional<DataTableColumn> dataDataOpt = columnDefList.stream().filter(x -> (SystemConstants.COLUMN_NAME_DATA_DATE.equalsIgnoreCase(x.getColumnCode())
                || SystemConstants.COLUMN_NAME_RUN_DATA_DATE.equalsIgnoreCase(x.getColumnCode())) && SystemConstants.COMMON_TRUE.equalsIgnoreCase(x.getShowFlag())).findFirst();
        Optional<DataTableColumn> runBatchIdOpt = columnDefList.stream().filter(x -> SystemConstants.COLUMN_NAME_RUN_BATCH_ID.equals(x.getColumnCode()))
                .findFirst();
        Optional<DataTableColumn> bizBranchCodeOpt = columnDefList.stream().filter(x -> "FIN_BRANCH_CODE".equals(x.getColumnCode()))
                .findFirst();
        Optional<DataTableColumn> mngBranchCodeOpt = columnDefList.stream().filter(x -> "MNG_BRANCH_CODE".equals(x.getColumnCode()))
                .findFirst();

        StringBuilder sb = new StringBuilder("select ");
        String cols = columnDefList.stream().filter(x -> "T".equals(x.getShowFlag())).map(DataTableColumn::getColumnCode).collect(Collectors.joining(","));
        if (StringUtils.isEmpty(cols)) {
            cols = " * ";
        }
        sb.append(cols)
                .append(" from ");
        if (StringUtils.isNotEmpty(dataTable.getSchemaName())) {
            sb.append(dataTable.getSchemaName()).append(".");
        }
        sb.append(tableCode).append(" t ");
        Map<String, Object> paramValues = new LinkedHashMap<>();
        StringBuilder sbWhere = new StringBuilder();
        if (dataDataOpt.isPresent() && StringUtils.isNotEmpty(dataDate)) {
            String dateStr = "replace(" + dataDataOpt.get().getColumnCode() + ",'-','')";
            sbWhere.append(dateStr).append("=?");
            paramValues.put("dataDate", dataDate);
        }
//            if (runBatchIdOpt.isPresent() && StringUtils.isNotEmpty(runBatchId)) {
//                if (sbWhere.length() > 0) {
//                    sbWhere.append(" and ");
//                }
//                sbWhere.append(runBatchIdOpt.get().getColumnCode()).append("=?");
//                paramValues.put("runBatchId", runBatchId);
//            }
        //exists  (select 1 from V_ORG_NAV a where FIN_BRANCH_CODE = a.FIN_BRANCH_CODE and DATA_DATE=a.DATA_DATE\n"
        LoginUser loginUser = AuthUtils.getLoginUser();
        String finOrgCode = CollectionUtils.isNotEmpty(loginUser.getBizOrgs()) ? (String) loginUser.getBizOrgs().get(0).getBranchCode() : "";
        String mngOrgCode = CollectionUtils.isNotEmpty(loginUser.getMngOrgs()) ? (String) loginUser.getMngOrgs().get(0).getBranchCode() : "";
        // 总行不过滤机构
        if (bizBranchCodeOpt.isPresent() && !hqFinBranch.equals(finOrgCode)
                && mngBranchCodeOpt.isPresent() && StringUtils.isNotEmpty(mngOrgCode) && !hqMngBranch.equals(mngOrgCode)) {
            if (sbWhere.length() > 0) {
                sbWhere.append(" and ");
            }
            sbWhere.append("(exists  (select 1 from V_ORG_NAV a where t.FIN_BRANCH_CODE = a.FIN_BRANCH_CODE \n" +
                    "\t  and (a.L1_FIN_BRANCH_CODE=? or a.FIN_BRANCH_CODE=?)) \n" +
                    "\t  or\n" +
                    "\t  exists  (select 1 from V_MGMT_ORG_NAV a where t.MGMT_BRANCH_CODE = a.MGMT_BRANCH_CODE \n" +
                    "\t  and (a.L1_MGMT_BRANCH_CODE=? or a.MGMT_BRANCH_CODE=?)) )");
            paramValues.put("bizBranchCode", finOrgCode);
            paramValues.put("bizBranchCode2", finOrgCode);
            paramValues.put("mngBranchCode", mngOrgCode);
            paramValues.put("mngBranchCode2", mngOrgCode);
        } else if (bizBranchCodeOpt.isPresent() && StringUtils.isNotEmpty(finOrgCode) && !hqFinBranch.equals(finOrgCode)) {
            if (sbWhere.length() > 0) {
                sbWhere.append(" and ");
            }
            sbWhere.append("exists  (select 1 from V_ORG_NAV a where t.FIN_BRANCH_CODE = a.FIN_BRANCH_CODE \n" +
                    "\t  and (a.L1_FIN_BRANCH_CODE=? or a.FIN_BRANCH_CODE=?)) \n");
            paramValues.put("bizBranchCode", finOrgCode);
            paramValues.put("bizBranchCode2", finOrgCode);
        } else if (mngBranchCodeOpt.isPresent() && StringUtils.isNotEmpty(mngOrgCode) && !hqMngBranch.equals(mngOrgCode)) {
            if (sbWhere.length() > 0) {
                sbWhere.append(" and ");
            }
            sbWhere.append("exists  (select 1 from V_MGMT_ORG_NAV a where t.MGMT_BRANCH_CODE = a.MGMT_BRANCH_CODE \n" +
                    "\t  and (a.L1_MGMT_BRANCH_CODE=? or a.MGMT_BRANCH_CODE=?)) ");
            paramValues.put("mngBranchCode", mngOrgCode);
            paramValues.put("mngBranchCode2", mngOrgCode);
        }

        //参数格式  columnCode, op, value
        StringBuilder orderClause = new StringBuilder();
        for (ColumnFilterVo param : queryParams) {
            String operator = param.getOperator();
            String columnCode = param.getColumnCode();
            if (param.getValue() != null && param.getValue().toString().length() > 0) {
                String filter;
                if (param.getValue().getClass().getName().contains("Array")) {
                    ArrayList<Object> value = (ArrayList<Object>) param.getValue();
                    if (value.size() == 0) {
                        continue;
                    }
                    String res = "";
                    if (value.get(0).getClass().getName().contains("String")) {
                        for (int i = 0; i < value.size(); i++) {
                            if (i > 0) {
                                res += ",";
                            }
                            res += "'" + value.get(i) + "'";
                        }
                    } else {
                        res = value.toString();
                    }
                    filter = columnCode + " in (" + res + ")";
                } else if ("LIKE".equalsIgnoreCase(operator)) {
                    filter = columnCode + " LIKE ?";
                    paramValues.put(columnCode, "%" + param.getValue() + "%");
                } else {
                    filter = columnCode + " = ?";
                    paramValues.put(columnCode, param.getValue());
                }
                if (sbWhere.length() > 0) {
                    sbWhere.append(" and ");
                }
                sbWhere.append(filter);
            }
            String sort = param.getSort();
            if (sort != null) {
                if (orderClause.length() > 0) {
                    orderClause.append(", ");
                }
                if ("ascending".equals(sort)) {
                    orderClause.append(columnCode).append(" asc");
                } else if ("descending".equals(sort)) {
                    orderClause.append(columnCode).append(" desc");
                }
            }
        }

        if (sbWhere.length() > 0) {
            sb.append(" where ").append(sbWhere.toString());
        }
        if (orderClause.length() > 0) {
            sb.append(" order by ").append(orderClause.toString());
        }
        // Order by
        List<String> orderByClause = new ArrayList<>();
        columnDefList.stream().forEachOrdered(x -> {
            if (StringUtils.equalsIgnoreCase(StringUtils.trim(x.getSortFlag()), "asc") || StringUtils.equalsIgnoreCase(StringUtils.trim(x.getSortFlag()), "desc")) {
                orderByClause.add(x.getColumnCode() + " " + x.getSortFlag());
            }
        });
        if (orderByClause.size() > 0) {
            sb.append(" order by ").append(StringUtils.join(orderByClause, SystemConstants.STRING_SPLITER));
        }

        log.info("sql for query={}", sb.toString());
        log.info("param={}", paramValues);

        // 字段选项值
        Map<String, List<CodeLabelVo>> dataOptions = new HashMap<>();
        for (DataTableColumn tableColDef : columnDefList) {
            if (StringUtils.isNotEmpty(tableColDef.getDataOptions())) {
                // 读取数据选项list
                String sql = tableColDef.getDataOptions();
                List<CodeLabelVo> options = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(CodeLabelVo.class));
                dataOptions.put(tableColDef.getColumnCode().toUpperCase(), options);
            }
        }

        //return pagedQueryWithCount(sb.toString(),pageIndex,pageSize,paramValues);
//		List<Map<String, Object>> resultList = pagedQuery(sb.toString(), pageIndex, pageSize, paramValues);
        StringBuilder sql = new StringBuilder(sb);
        List<Map<String, Object>> resultList = jdbcTemplate.query(sql.append(" limit " + page.getPageSize()).toString(), (rs, rowNum) -> {
            ResultSetMetaData metaData = rs.getMetaData();
            LinkedHashMap<String, Object> map = new LinkedHashMap<>();
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                String columnCode = metaData.getColumnName(i).toUpperCase();
                if (dataOptions.keySet().contains(columnCode)) {
                    List<CodeLabelVo> codeLabelVos = dataOptions.get(columnCode);
                    String code = String.valueOf(rs.getObject(i));
                    List<CodeLabelVo> collect = codeLabelVos.stream().filter(x -> StringUtils.equals(x.code(), code)).collect(Collectors.toList());
                    if (collect.size() > 0 && !StringUtils.equals(code, collect.get(0).label())) {
                        if (StringUtils.isEmpty(collect.get(0).label())) {
                            map.put(columnCode, String.format("%s", code));
                        } else {
                            map.put(columnCode, String.format("%s(%s)", collect.get(0).label(), code));
                        }
                    } else {
                        map.put(columnCode, code);
                    }
                } else {
                    if (rs.getObject(i) instanceof Number) {
                        map.put(metaData.getColumnName(i).toUpperCase(),
                                //new Number(String.valueOf(rs.getObject(i))).setScale(4, RoundingMode.HALF_UP).doubleValue());
                                rs.getObject(i));
                    } else {
                        map.put(metaData.getColumnName(i).toUpperCase(), rs.getObject(i));
                    }
                }
            }
            return map;
        }, paramValues.values().toArray());

        HashMap<String, Object> result = new HashMap();
        if (Integer.valueOf(0).equals(page.getPageIndex())) {
            String sqlCount = "select count(*) as CNT from (" + sb.toString() + ") as atab ";
            //		List<Map<String, Object>> countResult = pagedQuery(sqlCount, 0, 1, paramValues);
            List<Map<String, Object>> countResult = jdbcTemplate.queryForList(sqlCount, paramValues.values().toArray());
            int count = countResult.size() == 0 ? 0 : Integer.parseInt(countResult.get(0).get("CNT").toString());
            // result.put("total", resultList.size());
            result.put("total", count);
        }
        result.put("list", resultList);
        return result;
    }

//    @Override
//    @Transactional(readOnly = true)
//    //@Cacheable(key = "'DataTableColumn:' + methodName + args")
//    public List<DataTableColumn> getTableColumnDefListByTableCodeDimension(String tableCode) {
//        String key = "system_table_def_" + tableCode;
//        if (StringUtils.isEmpty(tableCode)) {
//            return new ArrayList<>();
//        }
//        List<DataTableColumn> tableColumnDefs = tableColumnDefRepository.findByTableCodeDimension(tableCode);
//        return tableColumnDefs;
//    }

//    @Override
//    @Transactional(readOnly = true)
//    public List<DataTable> getDimensionTableDefList() {
//        return this.getTableDefByGroupId().stream().filter(x -> {
//                    String tableCode = x.getTableCode();
//                    return ((tableCode.startsWith("S_D_") || tableCode.startsWith("D_"))
//                            && !this.ignoreDimensionList.contains(tableCode));
//                }).sorted(Comparator.comparing(DataTable::getTableCode))
//                .collect(Collectors.toList());
//    }

//    @Override
//    public Map<String, List<DataTableColumn>> getTableColumnDefMap() {
//      /*  if(this.tableColumnDefMap.size() == 0 ) {
//            init();
//        }*/
//        return this.tableColumnDefMap;
//    }

//    @Override
//    @Transactional(readOnly = true)
//    //@Cacheable(key = "'TableDefinationVO:' + methodName + args")
//    public List<TableDefinationVO> getRWAParameterCode(String packageCode) {
//        //如果是参数表，列出所有的参数tablecode
//        List<Object[]> paramTableCodeList = TableDefRepository.nativeGetParamTableCodeList(packageCode);
//        List<TableDefinationVO> list = BeanListUtils.castEntity(paramTableCodeList, TableDefinationVO.class);
//        return list;
//    }

//    @Override
//    @Transactional(readOnly = true)
//    //@Cacheable(key = "'TableDefinationVO:' + methodName + args")
//    public List<TableDefinationVO> getRWAParameterCodeDimension(String packageCode) {
//        //如果是参数表，列出所有的参数tablecode
//        List<Object[]> paramTableCodeList = TableDefRepository.nativeGetParamTableCodeListDimension(packageCode);
//        List<TableDefinationVO> list = BeanListUtils.castEntity(paramTableCodeList, TableDefinationVO.class);
//        return list;
//    }

//    /**
//     * 支持提供参数配置时维度表的列表
//     *
//     * @param baseTableCode, 如果是参数表，则是tableCode，否则是实体表名
//     * @param packageCode    ，     参数表需要提供参数集代码，
//     * @param dataDate       ，实体表提供日期
//     * @param columnList     , 实体表需要选择的字段
//     * @return
//     */
//    @Override
//    @Transactional(readOnly = true)
//    //@Cacheable(key = "'ParamCodeList:' + methodName + args")
//    public List<Map<String, Object>> getParamCodeList(String baseTableCode, String packageCode, String dataDate, String... columnList) {
//        List<Map<String, Object>> parameterDefList;
//
//        //S_D开头的表，使用packageCode
//        //D_开头的表，使用dataDate
//        //P_TABLE_PARAM_TBCODE,则取columnList中表示的TABLE_CODE
//        //国家和币种直接取参数表中的数据
//        //银行产品和缓释品需要叠加监管产品
//        if ("P_TABLE_PARAM_TBCODE".equalsIgnoreCase(baseTableCode)) {
//            //Preconditions.checkArgument(columnList.length==1,"输入的参数表table_code非法");
//            parameterDefList = jdbcTemplate.queryForList("SELECT PARAMETER,PARAMETER_NAME from P_TABLE_PARAMETER" +
//                            " where TABLE_CODE=? order by seq_num", columnList[0])
//                    .stream()
//                    .distinct()
//                    .collect(Collectors.toList());
//        } else if ("D_FIN_BRANCH".equalsIgnoreCase(baseTableCode)) {
//            parameterDefList = jdbcTemplate.queryForList("SELECT FIN_BRANCH_CODE as value, FIN_BRANCH_NAME as name FROM D_FIN_BRANCH" +
//                    " WHERE DATA_DATE= ? AND L2_FIN_BRANCH_CODE IS null", dataDate);
//        } else if (baseTableCode.startsWith("D_")) {
//            String[] columns;
//            //查找D_开头的代码
//            if (sParamCodeMapColumns.containsKey(baseTableCode)) {
//                columns = sParamCodeMapColumns.get(baseTableCode);
//            } else {
//                //                Preconditions.checkArgument(columnList.length == 2, "输入的参数表table_code非法");
//                columns = columnList;
//            }
//            if (columns == null || columns.length <= 0) {
//                return new ArrayList<>();
//            }
//            String baseSql = "SELECT " + columns[0] + " as value, " + columns[1] + " as name FROM " + baseTableCode;
//            String actualQuerySQL = baseSql + " WHERE DATA_DATE=? ";
//            // 按照表中的最大日期来查询
//            if ("D_CUST_RATING_MAINSCALE".equalsIgnoreCase(baseTableCode)) {
//                actualQuerySQL = baseSql + " WHERE DATA_DATE = (select max(data_date) from D_CUST_RATING_MAINSCALE where data_date <= ?) ";
//            }
//            if ("D_PRODUCT".equalsIgnoreCase(baseTableCode)) {
//                actualQuerySQL = baseSql + " WHERE DATA_DATE = (select max(data_date) from D_PRODUCT where data_date <= ?) ";
//            }
//            if ("D_CRM_TYPE".equalsIgnoreCase(baseTableCode)) {
//                actualQuerySQL = baseSql + " WHERE DATA_DATE = (select max(data_date) from D_CRM_TYPE where data_date <= ?) ";
//            }
//            if ("D_CUST_TYPE".equalsIgnoreCase(baseTableCode)) {
//                actualQuerySQL = baseSql + " WHERE DATA_DATE = (select max(data_date) from D_CUST_TYPE where data_date <= ?) ";
//            }
//            Optional<DataTable> tableDef = getTableDefByGroupId().stream().filter(x -> baseTableCode.equalsIgnoreCase(x.getTableCode())).findFirst();
//            if (tableDef.isPresent()) {
//                if (StringUtils.isNotBlank(tableDef.get().getDefaultOrderCol())) {
//                    actualQuerySQL += " ORDER BY " + tableDef.get().getDefaultOrderCol() + " " + tableDef.get().getDefaultOrderType();
//                }
//            }
//            parameterDefList = jdbcTemplate.queryForList(actualQuerySQL, dataDate);
//        } else if (baseTableCode.startsWith("S_D_")) {
//            String[] columns;
//            if (sParamCodeMapColumns.containsKey(baseTableCode)) {
//                columns = sParamCodeMapColumns.get(baseTableCode);
//            } else {
//                //                Preconditions.checkArgument(columnList.length == 2, "输入的参数表table_code非法");
//                columns = columnList;
//            }
//            String baseSql = "SELECT " + columns[0] + " as value, " + columns[1] + " as name FROM " + baseTableCode;
//            String actualQuerySQL = baseSql + " WHERE PACKAGE_CODE=? ";
//            Optional<DataTable> tableDef = getTableDefByGroupId().stream().filter(x -> baseTableCode.equalsIgnoreCase(x.getTableCode())).findFirst();
//            if (tableDef.isPresent()) {
//                if (StringUtils.isNotBlank(tableDef.get().getDefaultOrderCol())) {
//                    actualQuerySQL += " ORDER BY " + tableDef.get().getDefaultOrderCol() + " " + tableDef.get().getDefaultOrderType();
//                }
//            }
//            parameterDefList = jdbcTemplate.queryForList(actualQuerySQL, packageCode);
//        } else {
//            String baseSql = "SELECT " + columnList[0] + " as value, " + columnList[1] + " as name FROM " + baseTableCode;
//            String actualQuerySQL = baseSql + " WHERE PACKAGE_CODE=? ";
//            actualQuerySQL += " ORDER BY " + columnList[1];
//            parameterDefList = jdbcTemplate.queryForList(actualQuerySQL, packageCode);
//        }
//        return parameterDefList;
//    }
//
//    @Override
//    public List<Map<String, Object>> getParamCodeList(String baseTableCode, String packageCode) {
////        Preconditions.checkArgument(sParamCodeMapColumns.containsKey(baseTableCode), "非法的监管参数表名");
//        return getParamCodeList(baseTableCode, packageCode, "", sParamCodeMapColumns.get(baseTableCode)[0], sParamCodeMapColumns.get(baseTableCode)[1]);
//    }
//
//    @Override
//    public List<Map<String, Object>> getTblParamCodeList(String baseTableCode, String packageCode) {
//        return getParamCodeList("P_TABLE_PARAM_TBCODE", packageCode, "", baseTableCode);
//    }
//
//    /**
//     * 短期评级
//     */
//    @Override
//    @Transactional(readOnly = true)
//    public List<Map<String, Object>> getShortRatingCodeList(String packageCode) {
//        String sql = "select t.RATING_CODE,t.RATING_ORDER from S_D_RATING_ORDER t where t.rating_type='S'" +
//                " and t.package_code = ? ORDER BY t.RATING_ORDER";
//        return jdbcTemplate.queryForList(sql, packageCode);
//    }
//
//    /**
//     * 长期评级
//     */
//    @Override
//    @Transactional(readOnly = true)
//    public List<Map<String, Object>> getLongRatingCodeList(String packageCode) {
//        String sql = "select t.RATING_CODE,t.RATING_ORDER from S_D_RATING_ORDER t where rating_type='L'" +
//                " and rating_agency_code='SP' and package_code = ? ORDER BY t.RATING_ORDER";
//        return jdbcTemplate.queryForList(sql, packageCode);
//    }
}
